# !/usr/bin/env python
# -*- encoding: utf-8 -*-
# @Author : 大野
from sshtunnel import SSHTunnelForwarder
import pymysql
from itertools import chain
from common.cryptTool import CryptTools
from configparser import ConfigParser
import projectPath as path
config = ConfigParser()
config.read(path.projectPathUrl() + '/config/config.ini', encoding='utf-8')


class Sql:
    def __init__(self):
        self.work_env = config.get('ENV', 'work_env')

    def runSql(self, *args):
        global data5, server
        ct = CryptTools()
        if self.work_env == 'localhost':
            # 打开数据库连接
            server = SSHTunnelForwarder(("192.168.175.144", 22),  # 跳板机的地址
                                        ssh_username=str(ct.username("~xvClf4UPWdyGC1cD8QgBMA==~1~")),
                                        ssh_password=str(ct.password("~t8u4NVYlZjXa+bJlRNGpXw==~1~")),  # 跳板机访问密码
                                        remote_bind_address=(
                                            "pc-k2jvrk176f5418as0.rwlb.zhangbei.rds.aliyuncs.com", 3306))  # 目标数据库地址&端口
            server.start()
            conn = pymysql.connect(host="localhost",
                                   port=server.local_bind_port,  # 默认
                                   user="xxxxxx_admin",
                                   password="Hdv62GVkKc",
                                   database="loyalty2_qiushi6")
        else:
            conn = pymysql.connect(host='pc-k2jvrk176f5418as0.rwlb.zhangbei.rds.aliyuncs.com',
                                   port=3306,
                                   user="xxxxxx_admin",
                                   password="Hdv62GVkKc",
                                   database="loyalty2_qiushi6")
        cursor = conn.cursor()
        sql = 'select member_id from t_m_plat_member where id in {} and card_plan_id =320013'.format(args[0])
        # print('执行需要处理的member_id语句：', sql)
        cursor.execute(sql)
        data = cursor.fetchall()
        data2 = list(chain.from_iterable(data))
        # print(type(data2), data2)
        data1 = ",".join('%s' % id for id in data2)
        print(type(data1), '此次需要执行清理的member_id为：', data1)
        if not data1:
            pass
        else:
            sql1 = "select id from t_m_member_point where tenant_id='qiushi6' and card_plan_id =320013 and member_id in ({}) ".format(
                data1)
            # print('执行需要处理的member_id语句：', sql1)
            cursor.execute(sql1)
            data3 = cursor.fetchall()
            data4 = list(chain.from_iterable(data3))
            data5 = ",".join('%s' % id for id in data4)
        # print(type(data5), '此次需要执行清理的member_point_id为：', data5)
        table_name = ['t_m_member_grade',
                      't_m_mem_grade_record',
                      't_m_mem_point_record',
                      't_m_plat_member']
        mobile_name = ['$199$eme2MAJ8nikmltMBmPmm7g==$1$', '$133$8G9W16TEpXDNKSHi3v6tqg==$1$']
        for mobile in mobile_name:
            sql = "delete  from t_m_plain_encrypt_mobile where tenant_id='qiushi6' and mobile='{}' ".format(mobile)
            cursor.execute(sql)
            conn.commit()
            print('执行清理历史数据：', sql)

        if data1 != "":
            sql2 = "delete from t_m_member where tenant_id='qiushi6' and card_plan_id =320013 and id in ({})  ".format(
                data1)
            cursor.execute(sql2)
            conn.commit()
            # print('执行清理历史数据：', sql2)
            if data5 != "":
                sql3 = "delete from t_m_mem_point_rec_fast where tenant_id='qiushi6' and card_plan_id =320013 and member_point_id in ({})".format(
                    data5)
                cursor.execute(sql3)
                conn.commit()
            else:
                pass
            for name in table_name:
                sql = "delete from {} where tenant_id='qiushi6' and card_plan_id =320013 and member_id in (%s) ".format(
                    name)
                cursor.execute(sql % data1)
                print('执行清理历史数据：', sql % data1)
                conn.commit()

            print('=========历史数据已清除完成=========')
        else:
            print('=========历史数据已被清除=========')
        if self.work_env == 'localhost':
            conn.close()
            server.close()
        else:
            conn.close()


if __name__ == '__main__':
    # nick = ['~WLXAOZ1RXiOff4Cp98SRwJQSfHMjvQwqjDEPO6WLevI=~1~', '~I+nQA8mC1Y/6+WSNfJRXKg==~1~', '~L7CxAq/ZOqGjbxoPVFcsuQ==~1~']
    nick = ['~I+nQA8mC1Y/6+WSNfJRXKg==~1~',
            '~OpFAMtH3fNjHIF5hpYEWUp518sE9gFmGgWc+pDPndmw=~1~',
            '~iI4gIIIilnlRwNzLX+FeJjgaXvDrm7UelQ2dkbnCycg=~1~',
            '~hKayqBgFsKc+l1IM3W681cN0M95hLm6mREV0a7sqGk8=~1~',
            '~WmZwv9npHgLpAOus5xJGCxr+OaMER4CFsVboKU2Yen4=~1~',
            '~+3/k08CyoTOHB+sV7Mig0hwQNBjwIi9p7GaIfqQecZY=~1~',
            '~fKBbOvIdkqawuxreHJtje8d0TiKTTq9T8E2onZ7cqAU=~1~']
    Sql().runSql(tuple(nick))

